DROP TABLE IF EXISTS `examResult`;
CREATE TABLE `examResult`(
`examID` INT(10) UNSIGNED PRIMARY KEY auto_increment COMMENT '考试序号',
`examDate` datetime DEFAULT NOW() COMMENT '考试时间',
`score` FLOAT(6,2) NOT NULL COMMENT '分数',
`stuNo` INT(10) UNSIGNED NOT NULL COMMENT '考试学生',
`subjectNo` INT(10) UNSIGNED NOT NULL COMMENT '考试科目'
) ENGINE=INNODB COMMENT '考试成绩信息';

DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`(
`gradeID` INT(10) UNSIGNED PRIMARY KEY auto_increment COMMENT '年级编号',
`gradeName` VARCHAR(20) NOT NULL UNIQUE KEY COMMENT '年级名称'
) ENGINE=INNODB COMMENT '年级信息';

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`(
`stuNo` INT(10) UNSIGNED PRIMARY KEY auto_increment COMMENT '学生ID',
`stuName` VARCHAR(50) NOT NULL COMMENT '学生姓名',
`sex` enum('男','女') NOT NULL COMMENT '学生性别',
`telPhone` VARCHAR(20) DEFAULT NULL COMMENT '学生电话',
`gradeID` INT(10) UNSIGNED NOT NULL COMMENT '年级',
`enterDate` date DEFAULT NULL COMMENT '入学日期',
`birthday` date DEFAULT NULL COMMENT '生日',
`address` VARCHAR(100) DEFAULT NULL COMMENT '地址'
) ENGINE=INNODB COMMENT '学生信息';

DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject`(
`subjectNo` INT(10) UNSIGNED PRIMARY KEY auto_increment COMMENT '课程编号',
`subjectName` VARCHAR(100) NOT NULL COMMENT '课程名称',
`classHour` INT(10) UNSIGNED DEFAULT NULL COMMENT '课时',
`gradeID` INT(10) UNSIGNED COMMENT '年级编号'
) ENGINE=INNODB COMMENT '科目信息';
/*增加外键约束*/
ALTER TABLE examResult ADD CONSTRAINT fk_stuNo FOREIGN KEY(`stuNo`) REFERENCES `student`(`stuNo`);
ALTER TABLE examResult ADD CONSTRAINT fk_subjectNo FOREIGN KEY(`subjectNo`) REFERENCES `subject`(`subjectNo`);
ALTER TABLE `student` ADD CONSTRAINT fk_gradeID FOREIGN KEY(`gradeID`) REFERENCES `grade`(`gradeID`);
ALTER TABLE `subject` ADD CONSTRAINT fk_gradeID2 FOREIGN KEY(`gradeID`) REFERENCES `grade`(`gradeID`);
INSERT INTO `grade`(`gradeName`)VALUES('java'),('python'),('ruby'),('c#'),('php');/*增加年级*/
INSERT INTO `subject`(`subjectName`,`classHour`,`gradeID`)
VALUES('javaoop',20,1),('python基础',4,2),('ruby案例',4,3),('c#基础',8,4),('php基础',5,5);
INSERT INTO `student`(`stuName`,`sex`,`gradeID`,`birthday`,`address`)
VALUES('str1','男','1','1990-2-19','阜阳'),('str2','女','2','1980-2-19','安庆'),('str3','男','3','1992-2-19','合肥'),
('str4','男','4','1992-4-19','六安'),('str5','女','5','1996-6-19','阜阳'),('str6','男','1','1991-2-13','六安'),
('str7','女','3','1991-5-19','阜阳'),('str8','男','4','1990-2-19','阜阳'),('str9','女','5','1990-2-15','六安某某区');
INSERT into `examResult`(`score`,`stuNo`,`subjectNo`)VALUES
(80,1,2),(85,4,1),(60,8,5),(81,3,4),(89,2,3);
/*查找地址是六安的分数*/
SELECT `score` AS '分数' FROM `examResult` WHERE `stuNo` IN (
SELECT `stuNo` FROM `student` WHERE `address`='六安'
);
/*查找每月最后一天出生的学生信息*/
INSERT INTO `student`(`stuName`,`sex`,`gradeID`,`birthday`,`address`)VALUES('str10','男','2','1990-7-31','火星');
SELECT * FROM `student` WHERE YEAR(birthday)<YEAR(ADDDATE(birthday,INTERVAL 1 DAY)) OR MONTH(birthday)<MONTH(ADDDATE(birthday,INTERVAL 1 DAY));

SELECT `score` FROM examResult WHERE subjectNo=2;
SELECT AVG(`score`) FROM examResult WHERE subjectNo=2;
SELECT SUM(`score`) FROM examResult WHERE subjectNo=2;
SELECT * FROM `student` WHERE `gradeID`=1 ORDER BY `birthday`;
SELECT * FROM `examResult` WHERE `subjectNo`=1 ORDER BY `examDate` DESC,`score` DESC;
SELECT `score` FROM `examResult` WHERE `subjectNo`=1 AND YEAR(`examDate`)='2017' ORDER BY `score` DESC LIMIT 5;
SELECT MAX(`score`),MIN(`score`) FROM `examResult` WHERE `subjectNo`=(SELECT `subjectNo` FROM `subject` WHERE `subjectName`='javaoop');
SELECT `stuName` FROM `student` WHERE stuNo NOT IN(
SELECT stuNo FROM examResult WHERE subjectNo=(SELECT subjectNo FROM `subject` WHERE subjectName='javaoop')
AND examDate=(SELECT MAX(`examDate`) FROM examResult WHERE subjectNo=(SELECT subjectNo FROM `subject` WHERE subjectName='javaoop')) 
);

SELECT * FROM `examResult` WHERE EXISTS(SELECT `gradeID` FROM student WHERE gradeID=2) AND
subjectNo=(SELECT subjectNo FROM `subject` WHERE gradeID=2);

SELECT COUNT(examID),AVG(score) FROM `examResult` WHERE subjectNo=3;

SELECT stuNo,score FROM `examresult` WHERE examDate<'2016-2-17' LIMIT 5;

SELECT * FROM student WHERE sex='女' ORDER BY birthday LIMIT 1,6;

SELECT MAX(score),MIN(score),AVG(score) FROM examresult WHERE examDate='2016-2-17';

SELECT YEAR(birthday),COUNT(*) FROM student GROUP BY YEAR(birthday) HAVING COUNT(*)>2;

SELECT * FROM student WHERE birthday=(SELECT MAX(birthday) FROM student);

SELECT * FROM student WHERE stuNo IN(SELECT stuNo FROM examResult WHERE subjectNo=(SELECT subjectNo FROM `subject` WHERE subjectName='javaoop'));

SELECT subjectName FROM `subject` WHERE gradeID=(SELECT gradeID FROM grade WHERE gradeName='java');

SELECT * FROM student WHERE stuNo NOT IN(SELECT stuNo FROM examResult WHERE subjectNo=(SELECT subjectNo FROM `subject` WHERE subjectName='javaoop'));

--3：找出所有80后的学生，即1980-1989之间出生的学生
SELECT * FROM student WHERE YEAR(birthday)<=1989 AND YEAR(birthday)>=1980;
--4:找出来自 合肥 地区，有考试低于60的学生信息
SELECT * FROM student WHERE stuNO IN(SELECT stuNo FROM examResult WHERE score<80) AND address='阜阳';

SELECT * FROM examResult WHERE EXISTS(SELECT 1 FROM student WHERE gradeID=2)
AND subjectNo IN(SELECT subjectNo FROM `subject` WHERE gradeID=2);

SELECT sex,COUNT(stuNo) AS '人数',MAX(birthday),MIN(birthday) FROM student GROUP BY sex

SELECT address,sex,COUNT(stuNo) FROM student GROUP BY address,sex

SELECT DISTINCT address FROM student

SELECT COUNT(stuNo),address FROM student GROUP BY address HAVING COUNT(stuNo)>2

SELECT stuNo as '学号',stuName FROM student UNION SELECT subjectNo,subjectName FROM `subject`

SELECT stu.*,e.score FROM student AS stu LEFT JOIN examresult AS e ON(stu.stuNo=e.stuNo)

SELECT s.subjectName,g.gradeID FROM `subject` AS s LEFT JOIN grade AS g ON(s.gradeId=g.gradeID)

--1:查询累计考试次数 超过3次的学生信息（全部）。
SELECT COUNT(e.stuNo) AS '考试次数',s.* FROM examresult AS e JOIN student AS s ON(e.stuNo=s.stuNo) GROUP BY s.stuName HAVING COUNT(e.stuNo)>3

--2：查询有过补考的学生信息（同一门课超过2次，认为是补考）
SELECT stu.stuNo AS `姓名`,stu.stuName,s.subjectName AS '科目名称',COUNT(e.stuNo) AS '次数' FROM examresult AS e INNER JOIN student AS stu ON(e.stuNo=stu.stuNo)
INNER JOIN `subject` AS s ON(e.subjectNo=s.subjectNo) GROUP BY stu.stuNo,s.subjectNo HAVING COUNT(e.stuNo)>2

--3：最近半年内入学的学生
SELECT * FROM student WHERE enterDate>=ADDDATE(CURDATE(),INTERVAL -6 MONTH)

--4: 查询年级信息，以及该年级的课程数，没有课程的也要显示，对应的课程数目为0
SELECT g.gradeName,COUNT(s.subjectNo) FROM `subject` AS s 
RIGHT JOIN grade AS g ON(s.gradeID=g.gradeID) GROUP BY g.gradeName

SELECT stu.stuNo AS `学号`,stu.stuName AS `姓名`,g.gradeName AS `年级名称`,s.subjectName AS '科目名称',e.examDate AS `考试日期`,e.score AS `成绩` FROM examresult AS e 
RIGHT JOIN student AS stu ON(e.stuNo=stu.stuNo)
LEFT JOIN `subject` AS s ON(e.subjectNo=s.subjectNo) 
LEFT JOIN `grade` AS g ON(s.gradeID=g.gradeID) 
GROUP BY stu.stuNo,s.subjectName 

SELECT g.*,(SELECT COUNT(*) FROM `subject`AS s WHERE s.gradeID=g.gradeID) as 课程数 FROM grade AS g

SELECT g.*,(SELECT COUNT(*) FROM `subject`AS s GROUP BY s.gradeID) as 课程数 FROM grade AS g

SELECT telPhone FROM student WHERE telPhone LIKE '1[3-5][0-9]'

SELECT gradeID,MAX(birthday) FROM student GROUP BY gradeID

SELECT * FROM student WHERE stuName LIKE '刘%飞%'

BEGIN;
INSERT INTO examResult(score,stuNo,subjectNo) VALUES (80,1,2),(86,2,3),(70,4,5)
SELECT * FROM examresult
COMMIT;

CREATE VIEW view_stu AS
SELECT stuName,telphone,YEAR(NOW())-YEAR(birthday) AS `生日` FROM student
SELECT * FROM view_stu

CREATE VIEW view_avg AS
SELECT stu.stuName,s.subjectName,AVG(e.score) FROM student as stu
JOIN examresult AS e ON(stu.stuNo=e.stuNo)
JOIN `subject` AS s ON(e.subjectNo=s.subjectNo)
GROUP BY stu.stuNo,s.subjectName

--3:设计高分成绩单视图，只包含成绩在80分以上的学生信息，包括科目名称以及成绩
CREATE VIEW view_socre AS
SELECT stu.*,s.subjectName,e.score FROM examresult AS e
JOIN student AS stu ON(e.stuNo=stu.stuNo)
JOIN `subject` AS s ON(e.subjectNo=s.subjectNo)
WHERE e.score>=80
ORDER BY e.score DESC

SELECT * FROM view_socre



